﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;

namespace ERP_RORZE_DLL
{
    public class SQLManagement
    {
        public string SQLConnectionString = null;
        public string DB2ConnectionString = null;
        public string HRConnectionString = null;
        public SQLManagement()
	    {
            SQLConnectionString = "";
            DB2ConnectionString = "";
            HRConnectionString = "";
	    }
        
        //---------------------------------------
        // Function for main database in SQL Server
        //---------------------------------------
        public DataSet SQL_Select(string myTable, string myCondition)
        {
            DataSet ret = new DataSet();
            SqlConnection myConnection = new SqlConnection(SQLConnectionString);
            try
            {
                myConnection.Open();
                string sql = "SELECT * FROM " + myTable + " WHERE " + myCondition;
                SqlCommand cmd = new SqlCommand(sql, myConnection);
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = cmd;
                adapter.Fill(ret);
                adapter.Dispose();
                cmd.Dispose();
                myConnection.Close();
            }
            catch //(Exception ex)
            {
                return null;
            }
            return ret;
        }
        public bool SQL_Update(string myTable, string setValue, string myCondition)
        {
            SqlConnection myConnection = new SqlConnection(SQLConnectionString);
            try
            {
                myConnection.Open();
                string sql = "UPDATE " + myTable + " SET "+ setValue +" WHERE " + myCondition;
                SqlCommand cmd = new SqlCommand(sql, myConnection);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                myConnection.Close();
            }
            catch //(Exception ex)
            {
                return false;
            }
            return true;
            
        }
        public bool SQL_Insert(string myTable, string columnSet, string myValues)
        {
            SqlConnection myConnection = new SqlConnection(SQLConnectionString);
            try
            {
                myConnection.Open();
                string sql = "INSERT INTO " + myTable + " (" + columnSet + ") values (" + myValues + ")";
                SqlCommand cmd = new SqlCommand(sql, myConnection);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                myConnection.Close();
            }
            catch //(Exception ex)
            {
                return false;
            }
            return true;
        }
        public bool SQL_Delete(string myTable, string myCondition)
        {
            SqlConnection myConnection = new SqlConnection(SQLConnectionString);
            try
            {
                myConnection.Open();
                string sql = "DELETE FROM " + myTable + " WHERE " + myCondition;
                SqlCommand cmd = new SqlCommand(sql, myConnection);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                myConnection.Close();
            }
            catch //(Exception ex)
            {
                return false;
            }
            return true;
        }
        public DataSet SQL_Query(string myQuery)
        {
            DataSet ret = new DataSet();
            SqlConnection myConnection = new SqlConnection(SQLConnectionString);
            try
            {
                myConnection.Open();                
                SqlCommand cmd = new SqlCommand(myQuery, myConnection);
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = cmd;
                adapter.Fill(ret);
                adapter.Dispose();
                cmd.Dispose();
                myConnection.Close();
            }
            catch //(Exception ex)
            {
                return null;
            }
            return ret;
        }
        public bool SQL_NonQuery(string myString)
        {            
            SqlConnection myConnection = new SqlConnection(SQLConnectionString);
            try
            {
                myConnection.Open();
                SqlCommand cmd = new SqlCommand(myString, myConnection);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                myConnection.Close();
            }
            catch //(Exception ex)
            {
                return false;
            }
            return true;
        }

        //-----------------------------------------
        // Function for Linked Database of DB2
        //----------------------------------------
        public DataSet DB2_Select(string myLibrary, string myTable, string myCondition)
        {
            DataSet ret = new DataSet();
            SqlConnection myConnection = new SqlConnection(DB2ConnectionString);
            try
            {
                myConnection.Open();
                string sql = "SELECT * FROM OPENQUERY("+ myLibrary +", 'SELECT * FROM "+ myTable +" WHERE "+ myCondition +"')";
                SqlCommand cmd = new SqlCommand(sql, myConnection);
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = cmd;
                adapter.Fill(ret);
                adapter.Dispose();
                cmd.Dispose();
                myConnection.Close();
            }
            catch //(Exception ex)
            {
                return null;
            }
            return ret;
        }
        public bool DB2_Update(string myLibrary, string myTable, string[] setColums, string[] setValues, string myCondition)
        {
            SqlConnection myConnection = new SqlConnection(DB2ConnectionString);
            try
            {
                myConnection.Open();
                string sql = "UPDATE OPENQUERY(" + myLibrary + ", 'SELECT ";
                for (int i = 0; i < (setColums.Length - 1); i++)
                {
                    sql += setColums[i] + ", ";
                }
                sql += setColums[setColums.Length-1]; 
                sql += " WHERE " + myCondition + "') SET ";
                for (int i = 0; i < (setColums.Length - 1); i++)
                {
                    sql += setColums[i] + "='" + setValues[i] +"', ";
                }
                sql += setValues[setColums.Length - 1] + "='" + setValues[setValues.Length - 1] + "'";
                SqlCommand cmd = new SqlCommand(sql, myConnection);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                myConnection.Close();
            }
            catch //(Exception ex)
            {
                return false;
            }
            return true;
        }
        public bool DB2_Insert(string myLibrary, string myTable, string[] setColums, string[] setValues)
        {
            SqlConnection myConnection = new SqlConnection(DB2ConnectionString);
            try
            {
                myConnection.Open();
                string sql = "INSERT OPENQUERY(" + myLibrary + ", 'SELECT ";
                for (int i = 0; i < (setColums.Length - 1); i++)
                {
                    sql += setColums[i] + ", ";
                }
                sql += setColums[setColums.Length - 1];
                sql += "') SELECT ";
                for (int i = 0; i < (setColums.Length - 1); i++)
                {
                    sql += "'" + setValues[i] + "', ";
                }
                sql += "'" + setValues[setValues.Length - 1] + "'";
                SqlCommand cmd = new SqlCommand(sql, myConnection);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                myConnection.Close();
            }
            catch //(Exception ex)
            {
                return false;
            }
            return true;
        }
        public DataSet DB2_Query(string myQuery)
        {
            DataSet ret = new DataSet();
            SqlConnection myConnection = new SqlConnection(SQLConnectionString);
            try
            {
                myConnection.Open();
                SqlCommand cmd = new SqlCommand(myQuery, myConnection);
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = cmd;
                adapter.Fill(ret);
                adapter.Dispose();
                cmd.Dispose();
                myConnection.Close();
            }
            catch //(Exception ex)
            {
                return null;
            }
            return ret;
        }
        public bool DB2_NonQuery(string myString)
        {
            SqlConnection myConnection = new SqlConnection(SQLConnectionString);
            try
            {
                myConnection.Open();
                SqlCommand cmd = new SqlCommand(myString, myConnection);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                myConnection.Close();
            }
            catch //(Exception ex)
            {
                return false;
            }
            return true;
        }
    }
}
